/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import beans.producto;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author pcsijbug
 */
public class DAOProducto {

    PreparedStatement ptmt = null;
    ResultSet resulSet = null;
    Connection conexion = null;

    private Connection getConnection() throws SQLException {
        Connection conn;
        //BDConexFactory fact = new BDConexFactory(dataSource);
        conn = BDConexFactory.getInstance().getConnection();
        return conn;
    }

    public List buscarProducto(String consulta) throws SQLException {

        List listaProduct = new ArrayList();

        try {

            conexion = getConnection();

            String sql = "select * from product where description like '%" + consulta + "%' order by product_id ";

            ptmt = conexion.prepareStatement(sql);

            // ptmt.setString(1, consulta);

            resulSet = ptmt.executeQuery();

            while (resulSet.next()) {
                producto prd = new producto();
                prd.setPRODUCT_ID(resulSet.getInt("PRODUCT_ID"));
                prd.setPRODUCT_CODE(resulSet.getString("PRODUCT_CODE"));
                prd.setQUANTITY_ON_HAND(resulSet.getInt("QUANTITY_ON_HAND"));
                prd.setMARKUP(resulSet.getDouble("MARKUP"));
                prd.setAVAILABLE(resulSet.getString("AVAILABLE"));
                prd.setDESCRIPTION(resulSet.getString("DESCRIPTION"));
                prd.setMANUFACTURER_ID(resulSet.getInt("MANUFACTURER_ID"));
                
                listaProduct.add(prd);
            }

        } catch (Exception e) {
            System.out.println(e.getMessage() + "---------------------------------------------------->");
        } finally {

            try {
                if (resulSet != null) {
                    resulSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAOArancel buscarArancelBD()"
                        + e.getMessage());


            } finally {

                try {
                    if (ptmt != null) {
                        ptmt.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }
                try {
                    if (conexion != null) {
                        conexion.close();
                        System.out.println("DAOArancel buscarArancelBD() cerrando conexion a  Base de Datos");

                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }

            }

            return listaProduct;

        }
    }

    public boolean agregarProducto(producto bean) throws SQLException {

        int intRes = 0;

        try {

            conexion = getConnection();
            
            String sql = "insert into product(" + 
                            " PRODUCT_ID, MANUFACTURER_ID, PRODUCT_CODE, PURCHASE_COST,QUANTITY_ON_HAND, MARKUP, " +
                            " AVAILABLE, DESCRIPTION) values(?,?,?,?,?,?,?,?)";
            
            System.out.println(sql);
            
            ptmt = conexion.prepareStatement(sql);

            ptmt.setInt(1, bean.getPRODUCT_ID());
            ptmt.setInt(2, bean.getMANUFACTURER_ID());
            ptmt.setString(3, bean.getPRODUCT_CODE());
            ptmt.setDouble(4, bean.getPURCHASE_COST());
            ptmt.setInt(5, bean.getQUANTITY_ON_HAND());
            ptmt.setDouble(6, bean.getMARKUP());
            ptmt.setString(7, bean.getAVAILABLE());
            ptmt.setString(8, bean.getDESCRIPTION());

            intRes = ptmt.executeUpdate();
            System.out.println(intRes + " resultado ");

        } catch (Exception e) {
            System.out.println(e.getMessage()  + "--------------------------->error sql");
            intRes = 0;
        } finally {

            try {
                if (resulSet != null) {
                    resulSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAOProducto agregarProducto()"
                        + e.getMessage());


            } finally {

                try {
                    if (ptmt != null) {
                        ptmt.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAOProducto agregarProducto()"
                            + e.getMessage());


                }
                try {
                    if (conexion != null) {
                        conexion.close();
                        System.out.println("DAOProducto agregarProducto() cerrando conexion a  Base de Datos");

                    }
                } catch (SQLException e) {
                    System.out.println("DAOProducto agregarProducto()"
                            + e.getMessage());


                }

            }

            Boolean boRes = false;
            if(intRes == 0)
                boRes = false;
            else
                 boRes = true;
            
            
            return boRes;

        }




    }
    
    public boolean ModificarProducto(producto bean) throws SQLException {

        int intRes = 0;

        try {

            conexion = getConnection();

            String sql = "update product set " + 
                    " PURCHASE_COST = ?, " +
                    " QUANTITY_ON_HAND = ?, " +
                    " MARKUP = ?, " +
                    " AVAILABLE = ?, " +
                    " DESCRIPTION = ? " +
                    " where PRODUCT_ID = ?";
            
            System.out.println(sql);
            
            ptmt = conexion.prepareStatement(sql);
            
            //ptmt.setInt(1, bean.getMANUFACTURER_ID());
            //ptmt.setString(2, bean.getPRODUCT_CODE());
            ptmt.setDouble(1, bean.getPURCHASE_COST());
            ptmt.setInt(2, bean.getQUANTITY_ON_HAND());
            ptmt.setDouble(3, bean.getMARKUP());
            ptmt.setString(4, bean.getAVAILABLE());
            ptmt.setString(5, bean.getDESCRIPTION());
            ptmt.setInt(6, bean.getPRODUCT_ID());
            
            intRes = ptmt.executeUpdate();
            
            System.out.println(intRes + " resulstados----------------------->");

        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {

            try {
                if (resulSet != null) {
                    resulSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAOArancel ModificarProducto()   -------->><"
                        + e.getMessage());
                intRes = 0;

            } finally {

                try {
                    if (ptmt != null) {
                        ptmt.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel ModificarProducto()"
                            + e.getMessage());


                }
                try {
                    if (conexion != null) {
                        conexion.close();
                        System.out.println("DAOArancel ModificarProducto() cerrando conexion a  Base de Datos");

                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel ModificarProducto()"
                            + e.getMessage());


                }

            }
            
            Boolean boRes = false;
            if(intRes == 0)
                boRes = false;
            else
                 boRes = true;
            
            
            return boRes;

        }




    }
    
    public boolean EliminarProducto(producto bean) throws SQLException {

        boolean bRes = false;

        try {

            conexion = getConnection();

            String sql = "delete product where PRODUCT_ID = ? ";

            ptmt = conexion.prepareStatement(sql);

            ptmt.setInt(1, bean.getPRODUCT_ID());


            bRes = ptmt.execute();


        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {

            try {
                if (resulSet != null) {
                    resulSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAOArancel buscarArancelBD()"
                        + e.getMessage());


            } finally {

                try {
                    if (ptmt != null) {
                        ptmt.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }
                try {
                    if (conexion != null) {
                        conexion.close();
                        System.out.println("DAOArancel buscarArancelBD() cerrando conexion a  Base de Datos");

                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }

            }

            return bRes;

        }
    }

    public producto buscarxCodigo(int codigo) throws SQLException {

        producto prd = new producto();

        try {

            conexion = getConnection();

            String sql = "select * from product where PRODUCT_ID = ? ";

            ptmt = conexion.prepareStatement(sql);

            ptmt.setInt(1, codigo);

            resulSet = ptmt.executeQuery();

            while (resulSet.next()) {

                prd.setPRODUCT_ID(resulSet.getInt("PRODUCT_ID"));
                prd.setPRODUCT_CODE(resulSet.getString("PRODUCT_CODE"));
                prd.setQUANTITY_ON_HAND(resulSet.getInt("QUANTITY_ON_HAND"));
                prd.setMARKUP(resulSet.getDouble("MARKUP"));
                prd.setAVAILABLE(resulSet.getString("AVAILABLE"));
                prd.setDESCRIPTION(resulSet.getString("DESCRIPTION"));
                prd.setMANUFACTURER_ID(resulSet.getInt("MANUFACTURER_ID"));
            }

        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {

            try {
                if (resulSet != null) {
                    resulSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAOArancel buscarArancelBD()"
                        + e.getMessage());


            } finally {

                try {
                    if (ptmt != null) {
                        ptmt.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }
                try {
                    if (conexion != null) {
                        conexion.close();
                        System.out.println("DAOArancel buscarArancelBD() cerrando conexion a  Base de Datos");

                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }

            }

            return prd;

        }

    }
}
